package ref;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class DemostictHealth extends AAnalysisHandle {

	private String targetTable = "domestic_health_product";
	
	public String getHandleName() {
		return "���ʳƷ";
	}

	public String getTargetTable() {
		return targetTable;
	}
	
	public void handleTransfer(String path, Connection oracleConn)throws Exception {
		PreparedStatement pstmt = null;
		try{
			int index = 0;
			int seq = 0;
			pstmt = oracleConn.prepareStatement("insert into "+ targetTable +" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			oracleConn.setAutoCommit(false);
			InputStream is = new FileInputStream(new File(path));
			Workbook workbook = WorkbookFactory.create(is);
			Sheet sheet = workbook.getSheetAt(0);
			Row row ;
				for(int i = 1; i <= sheet.getLastRowNum(); i++) {
					row = sheet.getRow(i);
					pstmt.setInt(1, ++seq);
					pstmt.setString(2, row.getCell(0).getStringCellValue().trim());
					pstmt.setString(3, row.getCell(1).getStringCellValue().trim());
					pstmt.setString(4, row.getCell(2).getStringCellValue().trim());
					pstmt.setString(5, row.getCell(3).getStringCellValue().trim());
					pstmt.setString(6, row.getCell(4).getStringCellValue().trim());
					pstmt.setString(7, row.getCell(5).getStringCellValue().trim());
					pstmt.setString(8, row.getCell(6).getStringCellValue().trim());
					pstmt.setString(9, row.getCell(7).getStringCellValue().trim());
					pstmt.setString(10, row.getCell(8).getStringCellValue().trim());
					pstmt.setString(11, row.getCell(9).getStringCellValue().trim());
					pstmt.setString(12, row.getCell(10).getStringCellValue().trim());
					pstmt.setString(13, row.getCell(11).getStringCellValue().trim());
					pstmt.setString(14, row.getCell(12).getStringCellValue().trim());
					pstmt.setString(15, row.getCell(13).getStringCellValue().trim());
					if (!("").equals(row.getCell(14,Row.CREATE_NULL_AS_BLANK).getStringCellValue())) {
						pstmt.setDate(16, Date.valueOf(row.getCell(14,Row.CREATE_NULL_AS_BLANK).getStringCellValue().trim()));
					}else {
						pstmt.setString(16, "");
					}
//					pstmt.setString(16, row.getCell(14).getStringCellValue().trim());
					if (!("").equals(row.getCell(15,Row.CREATE_NULL_AS_BLANK).getStringCellValue())) {
						pstmt.setDate(17, Date.valueOf(row.getCell(15,Row.CREATE_NULL_AS_BLANK).getStringCellValue().trim()));
					}else {
						pstmt.setString(17, "");
					}
					pstmt.setString(18, row.getCell(16).getStringCellValue().trim());
					if (!("").equals(row.getCell(17,Row.CREATE_NULL_AS_BLANK).getStringCellValue())) {
						pstmt.setDate(19, Date.valueOf(row.getCell(17,Row.CREATE_NULL_AS_BLANK).getStringCellValue().trim()));
					}else {
						pstmt.setString(19, "");
					}
//					pstmt.setString(19, row.getCell(17).getStringCellValue().trim());
					pstmt.setString(20, row.getCell(18).getStringCellValue().trim());
					pstmt.setString(21, row.getCell(19).getStringCellValue().trim());
					pstmt.setString(22, row.getCell(20).getStringCellValue().trim());
					pstmt.setString(23, row.getCell(21).getStringCellValue().trim());
					pstmt.setString(24, row.getCell(22).getStringCellValue().trim());
					pstmt.setString(25, row.getCell(23).getStringCellValue().trim());
					if (!("").equals(row.getCell(24,Row.CREATE_NULL_AS_BLANK).getStringCellValue())) {
						pstmt.setDate(26, Date.valueOf(row.getCell(24,Row.CREATE_NULL_AS_BLANK).getStringCellValue().trim()));
					}else {
						pstmt.setString(26, "");
					}
					if (!("").equals(row.getCell(25,Row.CREATE_NULL_AS_BLANK).getStringCellValue())) {
						pstmt.setDate(27, Date.valueOf(row.getCell(25,Row.CREATE_NULL_AS_BLANK).getStringCellValue().trim()));
					}else {
						pstmt.setString(27, "");
					}
//					pstmt.setString(26, row.getCell(24).getStringCellValue().trim());
//					pstmt.setString(27, row.getCell(25).getStringCellValue().trim());
					pstmt.setString(28, row.getCell(26).getStringCellValue().trim());
					pstmt.setString(29, row.getCell(27).getStringCellValue().trim());
					pstmt.setString(30, row.getCell(28).getStringCellValue().trim());
//					pstmt.setString(31, row.getCell(29).getStringCellValue().trim());
//					System.out.println("29:"+row.getCell(29,Row.CREATE_NULL_AS_BLANK).getStringCellValue());
					if (!("").equals(row.getCell(29,Row.CREATE_NULL_AS_BLANK).getStringCellValue())) {
						pstmt.setDate(31, Date.valueOf(row.getCell(29,Row.CREATE_NULL_AS_BLANK).getStringCellValue().trim()));
					}else {
						pstmt.setString(31, "");
					}
					pstmt.setString(32, row.getCell(30).getStringCellValue().trim());
					if (!("").equals(row.getCell(31,Row.CREATE_NULL_AS_BLANK).getStringCellValue())) {
						pstmt.setDate(33, Date.valueOf(row.getCell(31,Row.CREATE_NULL_AS_BLANK).getStringCellValue().trim()));
					}else {
						pstmt.setString(33, "");
					}
//					pstmt.setString(33, row.getCell(31).getStringCellValue().trim());
					pstmt.setString(34, row.getCell(32).getStringCellValue().trim());
					pstmt.setDate(35, new java.sql.Date(new java.util.Date().getTime()));
					pstmt.addBatch();
					index++;
					
					if(index % 2000 == 0) {
						pstmt.executeBatch();
						OracleConn.log("已处理�?" + index + "】数据�?");
					}
				}
			is.close();
			
			pstmt.executeBatch();
			oracleConn.commit();
			oracleConn.setAutoCommit(true);
			OracleConn.log("共处理�?" + index + "】数据�?");
//			System.out.println("�?��序号�?+seq);
		} catch (Exception e) {
			e.printStackTrace(System.err);
			try {
				oracleConn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace(System.err);
			}
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
	}
}